﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace OleDbNamedParameters
{
    public static partial class CommandXT
    {
        /// <summary>
        /// Replaces named parameters with question marks and sorts parameters in System.Data.OleDb.OleDbCommand
        /// </summary>
        /// <param name="command"></param>
        /// <exception cref="System.ArgumentNullException"></exception>
        /// <exception cref="System.ArgumentOutOfRangeException"></exception>
        /// <exception cref="System.ArgumentException"></exception>
        /// <exception cref="System.InvalidOperationException"></exception>
        public static void UseNamedParameters(this OleDbCommand command)
        {
            command.ReturnNames();

            List<OleDbParameter> collection = new List<OleDbParameter>();
            List<OleDbParameter> added = new List<OleDbParameter>();
            foreach (var f in command.Parameters)
            {
                OleDbParameter param = f as OleDbParameter;
                int index = param.ParameterName.IndexOf("_duplicite");
                if (index > 0)
                    param.ParameterName = param.ParameterName.Replace(param.ParameterName.Substring(index), "");
                if (!collection.Where(i => i.ParameterName == param.ParameterName).Any())
                    collection.Add(param);
            }
            command.Parameters.Clear();

            var array = command.CommandText.Split('@');
            for (int i = 1; i < array.Length; i++)
            {
                #region Replace parameter
                var index = array[i].Length;
                var indexOfComma = array[i].IndexOf(',');
                var indexOfSpace = array[i].IndexOf(' ');
                var indexOfSemicolon = array[i].IndexOf(';');
                var indexOfParenthesis = array[i].IndexOf(')');

                if (indexOfComma > -1 && indexOfComma < index)
                {
                    index = indexOfComma;
                }
                if (indexOfParenthesis > -1 && indexOfParenthesis < index)
                {
                    index = indexOfParenthesis;
                }
                if (indexOfSemicolon > -1 && indexOfSemicolon < index)
                {
                    index = indexOfSemicolon;
                }
                if (indexOfSpace > -1 && indexOfSpace < index)
                {
                    index = indexOfSpace;
                }

                string paramName = "@" + array[i].Substring(0, index);
                array[i] = "?" + array[i].Remove(0, index);
                var parameters = collection.Where(c => c.ParameterName == paramName);
                if (parameters.Any())
                {
                    var param = parameters.First();
                    var newParam = new OleDbParameter(param.ParameterName + "_duplicite_" + i, param.OleDbType);
                    newParam.Direction = param.Direction;
                    newParam.DbType = param.DbType;
                    newParam.IsNullable = param.IsNullable;
                    newParam.Precision = param.Precision;
                    newParam.Scale = param.Scale;
                    newParam.Size = param.Size;
                    newParam.SourceColumn = param.SourceColumn;
                    newParam.SourceColumnNullMapping = param.SourceColumnNullMapping;
                    newParam.SourceVersion = param.SourceVersion;
                    newParam.Value = param.Value;
                    command.Parameters.Add(newParam);
                    if (!added.Contains(param))
                        added.Add(param);
                }
                else
                {
                    throw new ArgumentException(paramName + " was not found.");
                }
                #endregion
            }
            foreach (var param in collection)
                command.Parameters.Add(param);
            command.CommandText = String.Join("", array);
        }

        /// <summary>
        /// Replaces question marks with parameter names in System.Data.OleDb.OleDbCommand.Parameters
        /// </summary>
        /// <param name="command"></param>
        /// <exception cref="System.ArgumentNullException"></exception>
        /// <exception cref="System.ArgumentOutOfRangeException"></exception>
        /// <exception cref="System.ArgumentException"></exception>
        public static void ReturnNames(this OleDbCommand command)
        {
            List<OleDbParameter> collection = new List<OleDbParameter>();
            foreach (var f in command.Parameters)
            {
                OleDbParameter param = f as OleDbParameter;
                collection.Add(param);
            }

            var array = command.CommandText.Split('?');
            for (int i = 1; i < array.Length; i++)
            {
                OleDbParameter param = collection[i - 1];
                int index = param.ParameterName.IndexOf("_duplicite");
                if (index > 0)
                    param.ParameterName = param.ParameterName.Replace(param.ParameterName.Substring(index), "");
                array[i] = param.ParameterName + array[i];
            }
            command.CommandText = String.Join("", array);
        }
    }

}
